package com.yj.web.dao.mapper;

import com.yj.web.dao.po.admin.*;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Set;

@Component
public interface AdminUserMapper {
//    @Select("<script>" +
//            "select id as userInfoId, wechat_id as wechatId, idcard_no as IdNumber, created_at as registerTime from cus_user_info " +
//            "<trim prefix=\"where\" prefixOverrides=\"and | or\">" +
//            "<if test = \"IdNumber != ''\">" +
//            "instr(idcard_no, #{IdNumber}) " +
//            "</if>" +
//            "<if test = \"startTime != ''\">" +
//            "and created_at &gt; #{startTime} " +
//            "</if>" +
//            "<if test = \"endTime != ''\">" +
//            "and created_at &lt; #{endTime} " +
//            "</if>" +
//            "<if test = \"IdNumber != -1\">" +
//            "and deleted_flag = 'N' " +
//            "</if>" +
//            "</trim>" +
//            "</script>")
//    public List<AdminUser> listUserInfoByIdnumRegtime(@Param("IdNumber") String idNumber, @Param("startTime") String startTime, @Param("endTime") String endTime);

//    @Select("<script>" +
//            "select user_no as id, user_name as phone, user_info_id as userInfoId, nick_name as nickname from cus_user " +
//            "<trim prefix=\"where\" prefixOverrides=\"and | or\">" +
//            "<if test = \"ids != null\">" +
//            "user_info_id in " +
//            "<foreach item=\"id\" index=\"index\" collection=\"ids\" \n" +
//            "       open=\"(\" separator=\",\" close=\")\">\n" +
//            "       #{id}" +
//            "</foreach> " +
//            "</if>" +
//            "<if test = \"nickname != ''\">" +
//            "and instr(nick_name, #{nickname}) " +
//            "</if>" +
//            "<if test = \"phone != ''\">" +
//            "and instr(user_name, #{phone}) " +
//            "</if>" +
//            "<if test = \"phone != -1\">" +
//            "and deleted_flag = 'N' " +
//            "</if>" +
//            "</trim>" +
//            "order by id asc limit #{size} offset #{offset}" +
//            "</script>")
//    public List<AdminUser> listUserByIdPhoneNickname(@Param("phone") String phone, @Param("nickname") String nickname, @Param("ids") List<String> ids, @Param("offset") int offset, @Param("size") int size);

//    @Select("<script>" +
//            "select count(*) from cus_user " +
//            "<trim prefix=\"where\" prefixOverrides=\"and | or\">" +
//            "<if test = \"ids != null\">" +
//            "user_info_id in " +
//            "<foreach item=\"id\" index=\"index\" collection=\"ids\" \n" +
//            "       open=\"(\" separator=\",\" close=\")\">\n" +
//            "       #{id}" +
//            "</foreach> " +
//            "</if>" +
//            "<if test = \"nickname != ''\">" +
//            "and instr(nick_name, #{nickname}) " +
//            "</if>" +
//            "<if test = \"phone != ''\">" +
//            "and instr(user_name, #{phone}) " +
//            "</if>" +
//            "<if test = \"phone != -1\">" +
//            "and deleted_flag = 'N' " +
//            "</if>" +
//            "</trim>" +
//            "</script>")
//    public int countUserByIdPhoneNickname(@Param("phone") String phone, @Param("nickname") String nickname, @Param("ids") List<String> ids);

    //export
//    @Select("<script>" +
//            "select user_no as id, user_name as phone, user_info_id as userInfoId, nick_name as nickname from cus_user " +
//            "<trim prefix=\"where\" prefixOverrides=\"and | or\">" +
//            "<if test = \"ids != null\">" +
//            "user_info_id in " +
//            "<foreach item=\"id\" index=\"index\" collection=\"ids\" \n" +
//            "       open=\"(\" separator=\",\" close=\")\">\n" +
//            "       #{id}" +
//            "</foreach> " +
//            "</if>" +
//            "<if test = \"nickname != ''\">" +
//            "and instr(nick_name, #{nickname}) " +
//            "</if>" +
//            "<if test = \"phone != ''\">" +
//            "and instr(user_name, #{phone}) " +
//            "</if>" +
//            "<if test = \"phone != -1\">" +
//            "and deleted_flag = 'N' " +
//            "</if>" +
//            "</trim>" +
//            "order by id asc" +
//            "</script>")
//    public List<AdminUser> listUserByIdPhoneNicknameNoPage(@Param("phone") String phone, @Param("nickname") String nickname, @Param("ids") List<String> ids);

    //***************************************************************
    @Select("<script>" +
            "select cui.id as userInfoId, wechat_id as wechatId, idcard_no as IdNumber, cu.created_at as registerTime, user_no as id, user_name as phone, nick_name as nickname " +
            "from cus_user as cu left join cus_user_info as cui on cu.user_info_id = cui.id " +
            "<trim prefix=\"where\" prefixOverrides=\"and | or\">" +
            "<if test = \"IdNumber != ''\">" +
            "instr(idcard_no, #{IdNumber}) " +
            "</if>" +
            "<if test = \"startTime != ''\">" +
            "and cu.created_at &gt; #{startTime} " +
            "</if>" +
            "<if test = \"endTime != ''\">" +
            "and cu.created_at &lt; #{endTime} " +
            "</if>" +
            "<if test = \"nickname != ''\">" +
            "and instr(nick_name, #{nickname}) " +
            "</if>" +
            "<if test = \"phone != ''\">" +
            "and instr(user_name, #{phone}) " +
            "</if>" +
            "<if test = \"phone != -1\">" +
            "and cu.deleted_flag = 'N' and cui.deleted_flag = 'N' " +
            "</if>" +
            "</trim>" +
            "order by cu.created_at desc limit #{size} offset #{offset}" +
            "</script>")
    public List<AdminUser> listUserInfoByIdnumRegtimePhoneNickname(@Param("IdNumber") String idNumber, @Param("startTime") String startTime, @Param("endTime") String endTime,
                                                      @Param("phone") String phone, @Param("nickname") String nickname, @Param("offset") int offset, @Param("size") int size);

    @Select("<script>" +
            "select count(*) from cus_user as cu left join cus_user_info as cui on cu.user_info_id = cui.id " +
            "<trim prefix=\"where\" prefixOverrides=\"and | or\">" +
            "<if test = \"IdNumber != ''\">" +
            "instr(idcard_no, #{IdNumber}) " +
            "</if>" +
            "<if test = \"startTime != ''\">" +
            "and cu.created_at &gt; #{startTime} " +
            "</if>" +
            "<if test = \"endTime != ''\">" +
            "and cu.created_at &lt; #{endTime} " +
            "</if>" +
            "<if test = \"nickname != ''\">" +
            "and instr(nick_name, #{nickname}) " +
            "</if>" +
            "<if test = \"phone != ''\">" +
            "and instr(user_name, #{phone}) " +
            "</if>" +
            "<if test = \"phone != -1\">" +
            "and cu.deleted_flag = 'N' and cui.deleted_flag = 'N' " +
            "</if>" +
            "</trim>" +
            "</script>")
    public int countUserInfoByIdnumRegtimePhoneNickname(@Param("IdNumber") String idNumber, @Param("startTime") String startTime, @Param("endTime") String endTime,
                                                                   @Param("phone") String phone, @Param("nickname") String nickname);


    @Select("<script>" +
            "select cui.id as userInfoId, wechat_id as wechatId, idcard_no as IdNumber, cu.created_at as registerTime, user_no as id, user_name as phone, nick_name as nickname " +
            "from cus_user as cu left join cus_user_info as cui on cu.user_info_id = cui.id " +
            "<trim prefix=\"where\" prefixOverrides=\"and | or\">" +
            "<if test = \"IdNumber != ''\">" +
            "instr(idcard_no, #{IdNumber}) " +
            "</if>" +
            "<if test = \"startTime != ''\">" +
            "and cu.created_at &gt; #{startTime} " +
            "</if>" +
            "<if test = \"endTime != ''\">" +
            "and cu.created_at &lt; #{endTime} " +
            "</if>" +
            "<if test = \"nickname != ''\">" +
            "and instr(nick_name, #{nickname}) " +
            "</if>" +
            "<if test = \"phone != ''\">" +
            "and instr(user_name, #{phone}) " +
            "</if>" +
            "<if test = \"IdNumber != -1\">" +
            "and cu.deleted_flag = 'N' and cui.deleted_flag = 'N' " +
            "</if>" +
            "</trim>" +
            "order by cu.created_at desc " +
            "</script>")
    public List<AdminUser> listUserInfoByIdnumRegtimePhoneNicknameNoPage(@Param("IdNumber") String idNumber, @Param("startTime") String startTime, @Param("endTime") String endTime,
                                                                   @Param("phone") String phone, @Param("nickname") String nickname);
    //****************************************************************

    //账户信息 OK
    @Select("select user_no as id, nick_name as nickname, wechat_id as wechatId, cus_user.user_name as phone, idcard_no as IdNumber," +
            "cus_user.created_at as registerTime from cus_user left join cus_user_info on cus_user.user_info_id = cus_user_info.id where cus_user.deleted_flag = 'N' and cus_user.user_no = #{id}")
    public AdminUser findUserById(@Param("id") String id);

    //用户基本信息 OK
    @Select("select cu.user_no as userNo, cu.nick_name as nickname, cui.wechat_id as wechatId, cu.user_name as phone, cu.headpoto as portrait, cui.weibo, cui.qq," +
            "cu.last_login_time as lastLoginTime, cu.created_at as registerTime from cus_user as cu join cus_user_info as cui on cu.user_info_id = cui.id " +
            "where cu.deleted_flag = 'N' and cu.user_no = #{id}")
    public UserBaseInfo findUserBaseInfoById(@Param("id") String id);

    //账户资金信息 ok
    @Select("select distinct user_no as userNo, (balance_amt + freeze_amt)/100 as accountBalance, balance_amt/100 as availableBalance," +
//            "(cus_project_fundraising_detail.loan_amount-cus_project_fundraising_detail.repayment_amount) as receivablePrincipal," +
//            "(cus_project_fundraising_detail.profit_amount-cus_project_fundraising_detail.repayment_profit_amount) as receivableInterest," +
            "freeze_amt/100 as frozenCapital " +
            "from cus_account left join cus_user on cus_user.account_id = cus_account.id " +
            "left join cus_project_fundraising_detail on cus_user.id = cus_project_fundraising_detail.lender_user_id " +
            "where cus_user.deleted_flag = 'N' and cus_user.user_no = #{id}")
    public AccountCapitalInfo findAccountCapitalInfoById(@Param("id") String id);
    @Select("select IFNULL((sum(cpfd.loan_amount)-sum(cpfd.repayment_amount))/100,0) from cus_project_fundraising_detail as cpfd left join cus_user as cu on cpfd.lender_user_id = cu.id where cu.user_no = #{id} and cu.deleted_flag = 'N' and cpfd.deleted_flag = 'N' and cpfd.status in (0,1)")
    public double findReceivablePrincipalById(@Param("id") String id);
    @Select("select IFNULL((sum(cpfd.profit_amount)-sum(cpfd.repayment_profit_amount))/100,0) from cus_project_fundraising_detail as cpfd left join cus_user as cu on cpfd.lender_user_id = cu.id where cu.user_no = #{id} and cu.deleted_flag = 'N' and cpfd.deleted_flag = 'N' and cpfd.status in (0,1)")
    public double findReceivableInterestById(@Param("id") String id);

    //第三方支付信息 ok
    @Select("select user_no as userNo, real_name as realName, idcard_no as IdNumber, bank_card_no as bankCardNo, bank_phone_no as reservedPhone " +
            "from cus_user, cus_user_info where cus_user.deleted_flag = 'N' and cus_user.user_no = #{id} and cus_user.user_info_id = cus_user_info.id")
    public ThirdPartyPaymentInfo findThirdPartyPaymentInfoById(@Param("id") String id);

    //更换银行卡 ok
    @Update("update cus_user_info as cui, cus_user as cu set cui.bank_card_no=#{thirdPartyPaymentInfo.bankCardNo} where cui.id = cu.user_info_id and cu.user_no = #{thirdPartyPaymentInfo.userNo}")
    public boolean setBankCardNo(@Param("thirdPartyPaymentInfo") ThirdPartyPaymentInfo thirdPartyPaymentInfo);

    //投资记录
    @Select("select user_no as userNo, cus_project_fundraising_detail.id as investId, cus_fundraising_project.title as projectName," +
            "cus_project_fundraising_detail.loan_amount/100 as investment, cus_project_fundraising_detail.profit_amount/100 as interest," +
            "cus_project_fundraising_detail.repayment_amount/100 as receivedPrincipal, cus_project_fundraising_detail.repayment_profit_amount/100 as receivedInterest," +
            "cus_project_fundraising_detail.plan_refund_at as projectExpirationDate, cus_project_fundraising_detail.created_at as createdAt," +
            "cus_project_fundraising_detail.status as status " +
            "from cus_user, cus_project_fundraising_detail, cus_fundraising_project " +
            "where cus_user.deleted_flag = 'N' and cus_fundraising_project.deleted_flag = 'N' and cus_user.user_no = #{id} and " +
            "cus_project_fundraising_detail.fund_project_id = cus_fundraising_project.id and " +
            "cus_project_fundraising_detail.lender_user_id = cus_user.id " +
            "order by cus_project_fundraising_detail.id desc limit #{size} offset #{offset}")
    public List<InvestRecord> findInvestRecordById(@Param("id") String id, @Param("offset") int offset, @Param("size") int size);

    @Select("select count(*) " +
            "from cus_user, cus_project_fundraising_detail, cus_fundraising_project " +
            "where cus_user.deleted_flag = 'N' and cus_fundraising_project.deleted_flag = 'N' and cus_user.user_no = #{id} and " +
            "cus_project_fundraising_detail.fund_project_id = cus_fundraising_project.id and " +
            "cus_project_fundraising_detail.lender_user_id = cus_user.id ")
    public int countInvestRecordById(@Param("id") String id);

    //筹款记录
    //8月28日删除查询条件：cprp.status = 2，不记得当初为什么要加这个条件
    //9月4日添加查询条件：cprp.status = 2，计算 sum(cprp.principal_amount)和 sum(cprp.interest_amount) 已还本金和已还利息
    @Select("select user_no as userNo, cfp.id as fundraisingId, cfp.title as projectName, cfp.amount/100 as totalAmount, " +
            "cfp.cur_amount/100 as curAmount, cfp.total_interest/100 as projectInterest, " +
//            "(cfp.total_interest - IFNULL(sum(cprp.interest_amount),0))/100 as unpaidInterest, " +
            "cfp.repayment_date as projectExpirationDate, cfp.created_at as createdAt, cfp.status as status  " +
            "from cus_user as cu join cus_fundraising_project as cfp on cu.id = cfp.create_id " +
            "where cu.deleted_flag = 'N' and cfp.deleted_flag = 'N' " +
            "and cu.user_no = #{id} group by cfp.id order by cfp.id asc limit #{size} offset #{offset}")
    public List<FundraisingRecord> findFundraisingRecordById(@Param("id") String id, @Param("offset") int offset, @Param("size") int size);
    //获取待还本金
    @Select("select IFNULL((cfp.cur_amount - IFNULL(sum(cprp.principal_amount),0))/100,0) as unpaidPrincipal from cus_fundraising_project as cfp left join cus_project_repay_plan as cprp on cprp.fund_project_id = cfp.id where cfp.id = #{projectId} and cprp.status = 2")
    public double getUnpaidPrincipal(@Param("projectId") String projectId);
    //获取待还利息
    @Select("select IFNULL((cfp.total_interest - IFNULL(sum(cprp.interest_amount),0))/100,0) as unpaidPrincipal from cus_fundraising_project as cfp left join cus_project_repay_plan as cprp on cprp.fund_project_id = cfp.id where cfp.id = #{projectId} and cprp.status = 2")
    public double getUnpaidInterest(@Param("projectId") String projectId);
    //8月28日删除查询条件：cprp.status = 2，不记得当初为什么要加这个条件
    @Select("select count(distinct cfp.id) " +
            "from cus_user as cu left join cus_fundraising_project as cfp on cu.id = cfp.create_id, cus_project_repay_plan as cprp " +
            "where cu.deleted_flag = 'N' and cfp.deleted_flag = 'N' " +
            "and cu.user_no = #{id} and cprp.status = 2 and cprp.fund_project_id = cfp.id and cu.id=cprp.create_id ")//还款计划已还款
    public int countFundraisingRecordById(@Param("id") String id);

    //资金流水
    @Select("select distinct user_no as userNo, cad.id as changeId, cad.change_type as changeType, cad.change_amt/100 as changeAmount, cad.balance_amt/100 as balanceAmount," +
            "cad.created_at as createdAt, cad.updated_at as updatedAt " +
            "from cus_user as cu join cus_account as ca on cu.account_id = ca.id " +
            "join cus_account_detail as cad on ca.id = cad.account_id " +
            "where cu.deleted_flag = 'N' and ca.deleted_flag = 'N' and cad.deleted_flag = 'N' and cu.user_no = #{id} " +
            "order by cad.updated_at desc limit #{size} offset #{offset}")
    public List<CapitalChange> findCapitalChangeById(@Param("id") String id, @Param("offset") int offset, @Param("size") int size);

    @Select("select count(*) " +
            "from cus_user, cus_account_detail, cus_account, cus_project_fundraising_detail " +
            "where cus_user.deleted_flag = 'N' and cus_user.user_no = #{id} and cus_project_fundraising_detail.id = cus_account_detail.voucher_no and " +
            "cus_account_detail.account_id = cus_account.id and cus_project_fundraising_detail.lender_user_id = cus_user.id and " +
            "cus_account.id = cus_user.account_id")
    public int countCapitalChangeById(@Param("id") String id);

    /**
     *
     * @param id cus_account_detail.id = #{id}
     * @return
     */
    @Update("update cus_project_fundraising_detail,cus_account_detail,cus_account,cus_user " +
            "set cus_project_fundraising_detail.status=0 " +
            "where cus_account_detail.id = #{id} and cus_project_fundraising_detail.id = cus_account_detail.voucher_no and " +
            "cus_account_detail.account_id = cus_account.id and cus_project_fundraising_detail.lender_user_id = cus_user.id and cus_account.id = cus_user.account_id")
    public boolean updateCapitalChange(@Param("id") String id);

    @Insert("insert into cus_account_detail (id,created_at,updated_at,account_id,change_amt,balance_amt,change_type,bl_sign) values (#{capitalChange.changeId},now(),now(),#{capitalChange.account_id},#{capitalChange.changeAmount},#{capitalChange.balanceAmount},#{capitalChange.change_type},#{capitalChange.bl_sign})")
    public boolean insertCapitalChange(@Param("capitalChange") CapitalChange capitalChange);

    @Select("select user_name from cus_user where deleted_flag = 'N'")
    public Set<String> listAllUserPhone();

    @Select("select id from cus_user where deleted_flag = 'N' and user_no = #{user_no}")
    public String findUserIdByUserNo(@Param("user_no") String user_no);
    @Select("select user_name from cus_user where deleted_flag = 'N' and user_no = #{user_no}")
    public String findUserPhoneByUserNo(@Param("user_no") String user_no);

    //以分为单位
    @Select("select balance_amt from cus_account, cus_user where cus_account.deleted_flag = 'N' and cus_user.deleted_flag = 'N' and cus_user.id = #{lender_user_id} and cus_user.account_id = cus_account.id")
    public double findBalanceAmt(@Param("lender_user_id") String lender_user_id);

    /**
     *
     * @param id 用户id
     * @param balance_amt
     * @return
     */
    @Update("update cus_account, cus_user set cus_account.balance_amt = #{balance_amt}, cus_account.updated_at = now() where cus_user.deleted_flag = 'N' and cus_account.deleted_flag = 'N' and cus_user.account_id = cus_account.id and cus_user.id = #{id}")
    public int updateAccountBalanceAmt(@Param("id") String id, @Param("balance_amt") double balance_amt);

    @Select("select nick_name from cus_user where deleted_flag = 'N' and id = #{id}")
    public String getNicknameById(@Param("id") String id);

    @Select("select account_id from cus_user where deleted_flag = 'N' and id = #{id}")
    public String getAccountIdById(@Param("id") String id);
    @Select("select user_name from cus_user where deleted_flag = 'N' and id = #{id}")
    public String getUserPhoneById(@Param("id") String id);

    //提现
    @Select("<script>" +
            "select cuc.id, cu.user_no, cuc.cash_acc_name, cuc.cash_acc_tel, cuc.cash_bank_acc_no, cuc.cash_acc_idcard, cuc.cash_amount/100 as cash_amount, cuc.created_at, cuc.status, cu.nick_name, cu.user_name as user_phone " +
            "from cus_user_cash as cuc left join cus_user as cu on cuc.create_id = cu.id, cus_user_info as cui " +
            "<trim prefix=\"where\" prefixOverrides=\"and | or\">" +
            "<if test = \"real_name != ''\">" +
            "instr(cuc.cash_acc_name, #{real_name}) " +
            "</if>" +
            "<if test = \"startTime != ''\">" +
            "and cuc.created_at &gt; #{startTime} " +
            "</if>" +
            "<if test = \"endTime != ''\">" +
            "and cuc.created_at &lt; #{endTime} " +
            "</if>" +
            "<if test = \"phone != ''\">" +
            "and instr(cuc.cash_acc_tel, #{phone}) " +
            "</if>" +
            "<if test = \"status != null\">" +
            "and cuc.status = #{status} " +
            "</if>" +
            "<if test = \"offset != -1\">" +
            "and cu.user_info_id = cui.id and cu.deleted_flag = 'N' and cui.deleted_flag = 'N' and cuc.deleted_flag = 'N' " +
            "</if>" +
            "</trim>" +
            "order by cuc.created_at desc limit #{size} offset #{offset}" +
            "</script>")
    public List<DepositWithdraw> listDepositWithdraw(@Param("real_name") String real_name, @Param("startTime") String startTime, @Param("endTime") String endTime,
                                                     @Param("phone") String phone, @Param("status") Integer status, @Param("offset") int offset, @Param("size") int size);

    @Select("<script>" +
            "select count(*) from cus_user_cash as cuc left join cus_user as cu on cuc.create_id = cu.id, cus_user_info as cui " +
            "<trim prefix=\"where\" prefixOverrides=\"and | or\">" +
            "<if test = \"real_name != ''\">" +
            "instr(cui.real_name, #{real_name}) " +
            "</if>" +
            "<if test = \"startTime != ''\">" +
            "and cuc.created_at &gt; #{startTime} " +
            "</if>" +
            "<if test = \"endTime != ''\">" +
            "and cuc.created_at &lt; #{endTime} " +
            "</if>" +
            "<if test = \"phone != ''\">" +
            "and instr(cuc.cash_acc_tel, #{phone}) " +
            "</if>" +
            "<if test = \"status != null\">" +
            "and cuc.status = #{status} " +
            "</if>" +
            "<if test = \"offset != -1\">" +
            "and cu.user_info_id = cui.id and cu.deleted_flag = 'N' and cui.deleted_flag = 'N' and cuc.deleted_flag = 'N' " +
            "</if>" +
            "</trim>" +
            "</script>")
    public int countDepositWithdraw(@Param("real_name") String real_name, @Param("startTime") String startTime, @Param("endTime") String endTime,
                                                     @Param("phone") String phone, @Param("status") Integer status);

    //提现信息
    @Select("select cuc.id, cu.user_no, cuc.cash_acc_name, cuc.cash_acc_tel, cuc.cash_bank_acc_no,cuc.cash_bank_name, cuc.cash_acc_idcard, cuc.cash_amount/100 as cash_amount, cuc.created_at, cuc.status, cu.nick_name, cu.user_name as user_phone " +
            "from cus_user_cash as cuc left join cus_user as cu on cuc.create_id = cu.id, cus_user_info as cui " +
            "where cu.user_info_id = cui.id and cu.deleted_flag = 'N' and cui.deleted_flag = 'N' and cuc.deleted_flag = 'N' and cuc.id = #{id}")
    public DepositWithdraw findDepositWithdrawById(@Param("id") String id);

    @Update("update cus_user_cash set status = 3 where status = 0 and id = #{id}")
    public boolean updateDepositWithdrawStatus(@Param("id") String id);

    //提现更新账户的冻结资金
    @Update("update cus_account as ca, cus_user as cu set ca.freeze_amt = (ca.freeze_amt-100*#{cash_amount}) where cu.user_no=#{user_no} and cu.account_id=ca.id")
    public boolean updateFreezeAmt(@Param("user_no") String user_no, @Param("cash_amount") Double cash_amount);
}
